package gob.osinerg.emergencia.dao;

import gob.osinerg.emergencia.bean.AccidenteLeve;
import gob.osinerg.emergencia.bean.AccidenteLeveAccionCorrectiva;
import gob.osinerg.emergencia.bean.AccidenteLeveCausaBasica;
import gob.osinerg.emergencia.bean.AccidenteLeveCausaInmediata;
import gob.osinerg.emergencia.bean.AccidenteLeveDetalle;
import gob.osinerg.emergencia.bean.FormularioEmergenciaUnmay;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.hibernate.Session;
import org.hibernate.Transaction;

public class AccidenteLeveDAOImpl extends GenericDAOImpl implements AccidenteLeveDAO{

	public void save(AccidenteLeve bean, List detalleList,FormularioEmergenciaUnmay beanform) throws Exception {
		Session session = getSession();
		Transaction transaction = null;
		try {
			transaction = session.beginTransaction();
			
			if(beanform.getId() == null){
				beanform.setId(getNextPK("FRM_EMR_UM_SEQ"));
			}
			if(bean.getId() == null){
				bean.setId(getNextPK("ACCDNTE_LVE_SEQ"));
			}
			if(beanform.getNmroEmrgncia() == null){
				beanform.setNmroEmrgncia("R"+bean.getId());
			}
			session.saveOrUpdate(beanform);
			bean.setIdFormularioEmergencia(beanform.getId());
			session.saveOrUpdate(bean);
			
			for (int i = 0; i < detalleList.size(); i++) {
				AccidenteLeveDetalle detalle = (AccidenteLeveDetalle) detalleList.get(i);

				if(detalle.getId() == null){
					detalle.setId(getNextPK("ACCDNTE_LVE_DTLLE_SEQ"));
				}

				detalle.setIdAccidenteLeve(bean.getId());
				detalle.setNro(new Integer(i + 1));
				session.saveOrUpdate(detalle);
				List accionCorrectivaList = detalle.getAccionCorrectiva();
				if(accionCorrectivaList != null){
					for(int j=0; j < accionCorrectivaList.size(); j++){
						AccidenteLeveAccionCorrectiva beanCorrectiva = (AccidenteLeveAccionCorrectiva) accionCorrectivaList.get(j);
						beanCorrectiva.setIdAccidenteLeveDetalle(detalle.getId());
						session.saveOrUpdate(beanCorrectiva);
					}
				}
				List causasBasicasList = detalle.getListaCausasBasicas();
				if(causasBasicasList != null){
					for(int k=0; k < causasBasicasList.size(); k++){
						AccidenteLeveCausaBasica beanCausaBasica = (AccidenteLeveCausaBasica) causasBasicasList.get(k);
						beanCausaBasica.setIdAccidenteLeveDetalle(detalle.getId());
						session.saveOrUpdate(beanCausaBasica);
					}
				}
				
				List causasInmediatasList = detalle.getListaCausasInmediatas();
				if(causasInmediatasList != null){
					for(int m=0; m < causasInmediatasList.size(); m++){
						AccidenteLeveCausaInmediata beanCausaInmediata = (AccidenteLeveCausaInmediata) causasInmediatasList.get(m);
						beanCausaInmediata.setIdAccidenteLeveDetalle(detalle.getId());
						session.saveOrUpdate(beanCausaInmediata);
					}
				}
				
			}			
			transaction.commit();
		} catch (Exception e) {
			transaction.rollback();
			throw e;
		}finally{
			session.close();
		}
	}

    public Long getNextPK(String sequence) throws Exception {
        long result = 1;

        StringBuffer sql = new StringBuffer("");
        sql.append("SELECT ");
        sql.append(sequence);
        sql.append(".NEXTVAL AS ID FROM DUAL");        
        
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            pstmt = getSession().connection().prepareStatement(sql.toString());
            rs = pstmt.executeQuery();
            if(rs.next()) {
                result = rs.getLong(1);
            }
            pstmt.close();
            pstmt = null;
            rs.close();
            rs = null;
        }
        catch(Exception e) {
            log.error(e.getMessage());
            if(pstmt != null) {
                try {pstmt.close();} catch(Exception ignore) {}
            }
            if(rs != null) {
            	try {rs.close();}catch(Exception ignore) {}
            }
        }
        return new Long(result);
    }	
    
    public ArrayList getCausas(String causa) throws Exception{
    	ArrayList list = new ArrayList();
    	StringBuffer sql = new StringBuffer("");
        sql.append("SELECT trc.codigo, trc.codigo||'-'||trc.descripcion as descripcion from tablas_rdc_172_causas " +
        		"trc where trc.causa='"+causa+"'");
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            pstmt = getSession().connection().prepareStatement(sql.toString());
            rs = pstmt.executeQuery();
            while(rs.next()) {
               	 Map map = new HashMap();
               	 map.put("codigo", rs.getString(1));
               	 map.put("descripcion", rs.getString(2));
               	 list.add(map);
            }
            pstmt.close();
            pstmt = null;
            rs.close();
            rs = null;
        }
        catch(Exception e) {
            log.error(e.getMessage());
            if(pstmt != null) {
                try {pstmt.close();} catch(Exception ignore) {}
            }
            if(rs != null) {
            	try {rs.close();}catch(Exception ignore) {}
            }
        }
        return list;
    }

	public boolean verificarReporte(String mes, String anio) throws Exception {
		boolean resultado = false;
		StringBuffer sql = new StringBuffer("");
        sql.append("SELECT COUNT(*) FROM SFH_ACCDNTE_LVE A WHERE A.ANIO='"+anio+"' AND A.MES='"+mes+"'");
        
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            pstmt = getSession().connection().prepareStatement(sql.toString());
            rs = pstmt.executeQuery();
            while(rs.next()) {
            	//count++;
	            long l = rs.getLong(1);
	            if(l>0){
	            	resultado = true;
	            }
            }

            pstmt.close();
            pstmt = null;
            rs.close();
            rs = null;
        }
        catch(Exception e) {
            log.error(e.getMessage());
            if(pstmt != null) {
                try {pstmt.close();} catch(Exception ignore) {}
            }
            if(rs != null) {
            	try {rs.close();}catch(Exception ignore) {}
            }
        }
		
		return resultado;
	}

}
